import pandas as pd

# 创建 DataFrame
data = {
    "Fruits": ["Apple", "Orange", "Banana", "Mango", "Watermelon", "Grapefruit"],
    "Price": [3, 2, 2.5, 3.5, 2, 3.5]
}
df = pd.DataFrame(data)

# (1) 打印 DataFrame 的结果
print("DataFrame:")
print(df)

# (2) 分别使用 loc 和 iloc 抽出 “Orange” 一行数据
orange_loc = df.loc[df['Fruits'] == "Orange"]
orange_iloc = df.iloc[1]
print("\nOrange (loc):")
print(orange_loc)
print("\nOrange (iloc):")
print(orange_iloc)

# (3) 提取含有字符串 "Banana" 的行，输出返回值的数据类型
banana_row = df[df['Fruits'] == "Banana"]
print("\nBanana row:")
print(banana_row)
print("Data type:", type(banana_row))

# (4) 提取出 Price 最贵的水果所在的行
max_price_row = df[df['Price'] == df['Price'].max()]
print("\nMost expensive fruit row:")
print(max_price_row)

# (5) 提取出价格为 3 元的水果名称
fruits_with_price_3 = df[df['Price'] == 3]['Fruits']
print("\nFruits with price 3:")
print(fruits_with_price_3)

# (6) 添加一行数据 ['Peach', 5]
new_row = pd.DataFrame([["Peach", 5]], columns=["Fruits", "Price"])
df = pd.concat([df, new_row], ignore_index=True)
print("\nDataFrame after adding Peach:")
print(df)

# (7) 添加一列数据 “销量”，值自拟
df['Sales'] = [10, 20, 15, 10, 5, 12, 7]
print("\nDataFrame after adding Sales:")
print(df)

# (8) 添加一列数据 “总金额”，其值通过代码计算获得
df['TotalAmount'] = df['Price'] * df['Sales']
print("\nDataFrame after adding TotalAmount:")
print(df)

# (9) 将表单按照总金额从高到低重新排序
df_sorted = df.sort_values(by='TotalAmount', ascending=False)
print("\nDataFrame sorted by TotalAmount:")
print(df_sorted)

# (10) 分别使用 loc 和 iloc 抽出 Peach 和 Banana 行的数据
peach_banana_loc = df.loc[df['Fruits'].isin(["Peach", "Banana"])]
peach_banana_iloc = df.iloc[[df[df['Fruits'] == "Peach"].index[0], df[df['Fruits'] == "Banana"].index[0]]]
print("\nPeach and Banana (loc):")
print(peach_banana_loc)
print("\nPeach and Banana (iloc):")
print(peach_banana_iloc)

# (11) 提取并输出总金额高于所有总金额中位数的水果的单价
median_total_amount = df['TotalAmount'].median()
higher_than_median_prices = df[df['TotalAmount'] > median_total_amount]['Price']
print("\nPrices of fruits with TotalAmount higher than median:")
print(higher_than_median_prices)

# (12) 将表单的标题名修改为中文
pd.set_option('display.encoding', 'gbk')
df.columns = ['水果', '价格', '销量', '总金额']
print("\nDataFrame with Chinese column names:")
print(df)

# (13) 将 DataFrame 在当前目录下输出为 “Fruits price.csv” 文件，并利用 excel 打开查看
df.to_csv('Fruits price.csv', index=False,encoding='UTF-8')# 使用UTF-8编码，以防出现中文乱码

# (14) 在 excel 中将 “Fruits price.csv” 文件另存为 “Fruits price.xlsx”，再尝试读取操作，并输出前 5 行数据
df_excel = pd.read_excel('Fruits price.xlsx')
print("\nFirst 5 rows of Fruits price.xlsx:")
print(df_excel.head())